package com.daman.db


import xitrum.Log
import scala.collection.mutable.ArrayBuffer
import java.sql._
import com.daman.Configure


/**
 * Created by daman on 2015/2/2.
 */
object DBToolkit extends Log{

  val mysqlUrl = Configure.DBHost+"?user="+Configure.DBUser+"&password="+Configure.DBPassword+
    "&useUnicode=true&characterEncoding=utf8&autoReconnect=true&failOverReadOnly=false"

  def getConn() = {
    log.info("connect to "+Configure.DBHost)
    DriverManager.getConnection(mysqlUrl)
  }

  def closeConn(conn:Connection) ={
    log.info("close connect to "+Configure.DBHost)
    conn.close()
  }

  def getReadStatement(conn:Connection) ={
    conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)
  }

   // only read data
  def getReadPreparedStatement(conn: Connection,sql:String) = conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_READ_ONLY)

  // update data
  def getUpdatePrepareStatement(conn:Connection,sql:String) = conn.prepareStatement(sql,ResultSet.TYPE_FORWARD_ONLY,ResultSet.CONCUR_UPDATABLE)

  // return the auto increment id
  def getReturnIdPrepareStatement(conn:Connection,sql:String) = conn.prepareStatement(sql,Statement.RETURN_GENERATED_KEYS)
   /**
    * 从数据库读取数据 每条记录放到一个 Map[String,String] 中
    * @param conn  数据库连接
    * @param sql   查询的 sql 语句
    * @param params  sql 语句中的参数
    * @return List[Map[String,String]]
    */
  def readMap(conn:Connection,sql:String,params:List[String]):List[Map[String,String]] = {
    val statement = getReadPreparedStatement(conn,sql)
    var paramIndex = 0
    params.view.zipWithIndex.foreach{
      case (value,index) =>  {
        statement.setString(index+1,value)
        paramIndex = index + 1
      }
    }
    val resultSet = statement.executeQuery()
    val list = ArrayBuffer[Map[String,String]]()
    val resultSetMeta = resultSet.getMetaData
    while(resultSet.next()){
       var  map = Map[String,String]()
       for( index  <- 1 to resultSetMeta.getColumnCount ){
         val name = resultSetMeta.getColumnName(index)
         val value = resultSet.getObject(name)
         if( value != null )  map +=( name -> value.toString )
       }
       list += map
    }
    resultSet.close()
    statement.close()
    list.toList
  }

  /**
   *  从数据库中读取数据 采用传递函数的方式处理 resultSet
   * @param conn  数据库连接
   * @param sql   sql 语句
   * @param params  sql语句中的参数
   * @param f        处理resultSet 的函数
   * @return   List[Obj]
   */
  def readObj(conn:Connection,sql:String,params:List[String],f:(ResultSet)=>Any)  = {
    val statement = getReadPreparedStatement(conn,sql)
    params.view.zipWithIndex.foreach{
      case (value,index) => statement.setString(index+1,value)
    }
    val resultSet = statement.executeQuery()
    val list = ArrayBuffer[Any]()
    while(resultSet.next()){
       list += f(resultSet)
    }
    resultSet.close()
    list.toList
  }

  /**
   *  执行 更新 插入 删除 操作
   *  must be an SQL INSERT, UPDATE or DELETE statement; or an SQL statement that returns nothing
   *  The output will be in the form of int. This int value denotes the number of rows affected by the query.
   * @param conn
   * @param sql
   * @param params
   */
  def execute(conn:Connection,sql:String,params:List[String]) :Int = {
    val statement = getUpdatePrepareStatement(conn,sql)
    params.view.zipWithIndex.foreach{
      case (value,index) => statement.setString(index+1,value)
    }
    val out:Int = statement.executeUpdate()
    statement.close()
    out
  }


  /**
   * insert data into table and return the auto increment key
   * @param conn   database connection
   * @param tableName  table name
   * @param values  Map of field and it's value
   * @param safeFields  safe field like create_time field1,field2
   * @param safeValues  safe field value  like create_time value field1Value,field2Value
   * @return    the auto increment id
   */
  def insertReturnId(conn:Connection,tableName:String,values:Map[String,String],safeFields:String="",safeValues:String="")  = {
    var sql = "INSERT INTO "+tableName+ "("
    sql+=values.keys.mkString(",")
    if( !safeFields.isEmpty){
      sql += ","+safeFields
    }
    sql+=") VALUES ("
    val params = for( i <- values.keys.toList) yield "?"
    sql+=params.mkString(",")
    if( !safeValues.isEmpty){
      sql += ","+safeValues
    }
    sql+=")"
    val statement = getReturnIdPrepareStatement(conn,sql)
    var index = 1
    for(value <- values.values){
      statement.setString(index,value)
      index += 1
    }
    statement.executeUpdate()
    val resultSet = statement.getGeneratedKeys()
    var returnId = -1
    if(resultSet.next()){
      returnId = resultSet.getInt(1)
    }
    statement.close()
    returnId
  }

  /**
   *  批量执行 sql 为 更新和插入操作
   * @param conn 数据库连接
   * @param sql  sql语句
   * @param paramsArray  sql语句中的参数 为数组 数组中的每一个为一个完整的sql中的参数
   * @return 数组  每组参数执行后的返回结果
   */
  def executeAsBatch(conn:Connection,sql:String, paramsArray:List[List[String]] ) :List[Int]= {
    val statement = getUpdatePrepareStatement(conn,sql)
    for(params <- paramsArray ){
      params.view.zipWithIndex.foreach{
        case (value,index) => statement.setString(index+1,value)
      }
      statement.addBatch()
    }
    statement.executeBatch().toList
  }

  /**
   * update table record
   * @param conn   database connection
   * @param tableName   table name
   * @param paramsValue   Map update  field and it's value
   * @param paramsWhere   Map where condition  field and it's value
   * @param safeFieldValues  update field like update_time field1 = field1Value , field2 = field2Value
   * @return  returns either the row count for INSERT, UPDATE or DELETE statements, or 0 for SQL statements that return nothing
   */
  def update(conn:Connection,tableName:String,paramsValue:Map[String,String],paramsWhere:Map[String,String],safeFieldValues:String="")={
    var updateString = "UPDATE "+tableName+
                       " SET "
    updateString += paramsValue.keys.mkString(" = ? , ")+" = ? "
    if(! safeFieldValues.trim.isEmpty){
      updateString += ", "+safeFieldValues
    }
    updateString+=" where 1=1 "
    for((key,value) <-paramsWhere){
      updateString += " AND "+key+" = ? "
    }
    val statement = getUpdatePrepareStatement(conn,updateString)
    var index = 1
    for((key,value) <- paramsValue ){
      statement.setString(index,value)
      index +=1
    }
    for((key,value) <- paramsWhere){
      statement.setString(index,value)
      index+=1
    }
    val result = statement.executeUpdate()
    statement.close()
    result
  }


}
